﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using PMS.ENT;
using PMS.Utilities;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace PMS.DAL
{
    public class UserGroupDAL
    {
        #region Common
        private static UserGroupENT CreateUserGroupFromReader(IDataReader reader)
        {
            UserGroupENT item = new UserGroupENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.UserGroupID.ToString()))) item.UserGroupID = Utility.IntParse(reader[UserGroupColumns.UserGroupID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.UserID.ToString()))) item.UserID = Utility.IntParse(reader[UserGroupColumns.UserID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.GroupID.ToString()))) item.GroupID = Utility.IntParse(reader[UserGroupColumns.GroupID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[UserGroupColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[UserGroupColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[UserGroupColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[UserGroupColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[UserGroupColumns.Isdeleted.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.Status.ToString()))) item.Status = Utility.StringParse(reader[UserGroupColumns.Status.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.IsUploadFile.ToString()))) item.IsUploadFile = Utility.BoolParse(reader[UserGroupColumns.IsUploadFile.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserGroupColumns.IsDeleteFile.ToString()))) item.IsDeleteFile = Utility.BoolParse(reader[UserGroupColumns.IsDeleteFile.ToString()]);


            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static UserGroupENT GetUserGroupByID(int userGroupID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupGetByID.ToString());
                database.AddInParameter(dbCommand, "@UserGroupID", DbType.Int32, userGroupID);

                UserGroupENT userGroup = new UserGroupENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        userGroup = CreateUserGroupFromReader(reader);
                    }
                    reader.Close();
                }

                return userGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserGroupByID");
            }
        }

        public static UserGroupCollection GetUserGroupList(string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupGetList.ToString());
                UserGroupCollection listUserGroup = new UserGroupCollection();

                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listUserGroup.Add(CreateUserGroupFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listUserGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserGroupList");
            }
        }

        public static UserGroupCollection GetUserGroupListForCMB()
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupGetListForCMB.ToString());

                UserGroupCollection listUserGroup = new UserGroupCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listUserGroup.Add(CreateUserGroupFromReader(reader));
                    }
                    reader.Close();
                }

                return listUserGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserGroupListForCMB");
            }
        }

        public static UserGroupCollection GetUserGroupListByGroupID(int groupID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupGetListByGroupID.ToString());

                UserGroupCollection listUserGroup = new UserGroupCollection();
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listUserGroup.Add(CreateUserGroupFromReader(reader));
                    }
                    reader.Close();
                }

                return listUserGroup;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserGroupListForCMB");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertUserGroup(UserGroupENT userGroup, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupInsert.ToString());
                int userGroupID = 0;
                database.AddOutParameter(dbCommand, "@UserGroupID", DbType.Int32, userGroup.UserGroupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userGroup.UserID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, userGroup.GroupID);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, userGroup.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, userGroup.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, userGroup.Isdeleted);
                database.AddInParameter(dbCommand, "@Status", DbType.String, userGroup.Status);
                database.AddInParameter(dbCommand, "@IsUploadFile", DbType.Boolean, userGroup.IsUploadFile);
                database.AddInParameter(dbCommand, "@IsDeleteFile", DbType.Boolean, userGroup.IsDeleteFile);
                database.ExecuteNonQuery(dbCommand, trans);
                userGroupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@UserGroupID"));
                return userGroupID;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertUserGroup");
            }
        }

        public static int InsertUserGroup(UserGroupENT userGroup)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupInsert.ToString());
                int userGroupID = 0;
                database.AddInParameter(dbCommand, "@UserGroupID", DbType.Int32, userGroup.UserGroupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userGroup.UserID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, userGroup.GroupID);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, userGroup.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, userGroup.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, userGroup.Isdeleted);
                database.AddInParameter(dbCommand, "@IsUploadFile", DbType.Boolean, userGroup.IsUploadFile);
                database.AddInParameter(dbCommand, "@IsDeleteFile", DbType.Boolean, userGroup.IsDeleteFile);
                database.ExecuteNonQuery(dbCommand);
                userGroupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@UserGroupID"));
                return userGroupID;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertUserGroup");
            }
        }

        public static int UpdateUserGroup(UserGroupENT userGroup)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupUpdate.ToString());

                database.AddInParameter(dbCommand, "@UserGroupID", DbType.Int32, userGroup.UserGroupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userGroup.UserID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, userGroup.GroupID);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, userGroup.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, userGroup.Isdeleted);
                database.AddInParameter(dbCommand, "@IsUploadFile", DbType.Boolean, userGroup.IsUploadFile);
                database.AddInParameter(dbCommand, "@IsDeleteFile", DbType.Boolean, userGroup.IsDeleteFile);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    int userGroupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@UserGroupID"));
                    return userGroupID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertUserGroup");
            }
        }

        public static int UpdateUserGroup(UserGroupENT userGroup, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupUpdate.ToString());

                database.AddInParameter(dbCommand, "@UserGroupID", DbType.Int32, userGroup.UserGroupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userGroup.UserID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, userGroup.GroupID);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, userGroup.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, userGroup.Isdeleted);
                database.AddInParameter(dbCommand, "@IsUploadFile", DbType.Boolean, userGroup.IsUploadFile);
                database.AddInParameter(dbCommand, "@IsDeleteFile", DbType.Boolean, userGroup.IsDeleteFile);
                database.ExecuteNonQuery(dbCommand, trans);
                int userGroupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@UserGroupID"));
                return userGroupID;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_UpdateUserGroup");
            }
        }

        public static int DeleteUserGroup(string userGroupID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupDelete.ToString());
                database.AddInParameter(dbCommand, "@UserGroupID", DbType.String, userGroupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteUserGroup");
            }
        }

        public static int DeleteUserGroupNotInID(string userGroupID, int groupID, int userID, DbTransaction trans)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupDeleteNotInID.ToString());
                database.AddInParameter(dbCommand, "@UserGroupID", DbType.String, userGroupID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.ExecuteNonQuery(dbCommand, trans);
               
                    return 1;
               
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteUserGroup");
            }
        }

        public static int ConfirmUserGroup(string usergroupID, int userID,string status)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGroupConfirm.ToString());
                database.AddInParameter(dbCommand, "@UserGroupID", DbType.String, usergroupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_ConfirmUserGroup");
            }
        }
        #endregion
    }
}
